﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

using SLIITPro.Core;
using SLIITPro.Data.Entity;

namespace SLIITPro.Data.Extension
{
    public class CommentExtension
    {
        Datamanager _datamanager;
        public CommentExtension()
        {
            _datamanager = new Datamanager();
        }


        public void AddComment(Comments comments)
        {

            var query = Properties.Resources.queryAddComment;
            var parameter = new SqlParameter[7];
            parameter[0] = new SqlParameter("@1", SqlDbType.NVarChar) { Value = comments.Id };
            parameter[1] = new SqlParameter("@2", SqlDbType.Int) { Value = comments.BId };
            parameter[2] = new SqlParameter("@3", SqlDbType.NVarChar) { Value = comments.Author };
            parameter[3] = new SqlParameter("@4", SqlDbType.NVarChar) { Value = comments.Email };
            parameter[4] = new SqlParameter("@5", SqlDbType.DateTime) { Value = comments.DateCreated };
            parameter[5] = new SqlParameter("@6", SqlDbType.Text) { Value = comments.Content };
            parameter[6] = new SqlParameter("@7", SqlDbType.TinyInt) { Value = comments.IsApproved };
            _datamanager.ExecuteNonQuery(query, parameter);
        }

        public void DeleteCommentByCommentId(string id)
        {
            var query = Properties.Resources.queryDeleteComment;
            var parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@1", SqlDbType.NVarChar) { Value = id };
            _datamanager.ExecuteNonQuery(query, parameter);
        }

        public void DeleteCommentByBusinessId(int id)
        {
            var query = Properties.Resources.queryDeleteCommentByBusinessId;
            var parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@1", SqlDbType.Int) { Value = id };
            _datamanager.ExecuteNonQuery(query, parameter);
        }



        public List<Comments> SelectComment()
        {
            var query = Properties.Resources.querySelectComments;
            var table = _datamanager.ExecuteQuery(query, "Comments", null);
            var commentses = (from DataRow row in table.Rows
                              select new Comments(row)).OrderBy(c => c.DateCreated).ToList();
            return commentses;
        }

        public List<Comments> SelectCommentByUser(int id)
        {
            var query = SLIITPro.Data.Properties.Resources.querySelectCommentByUser;
            var parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@1", SqlDbType.Int) { Value = id };
            var table = _datamanager.ExecuteQuery(query, "Comments", parameter);
            var commentses = (from DataRow row in table.Rows
                              select new Comments(row)).OrderBy(c => c.DateCreated).ToList();
            return commentses;
        }

        public List<Comments> SelectComment(string context, string id)
        {
            var query = string.Format(Properties.Resources.querySelectComments + " WHERE {0}='{1}'", context, id);
            var table = _datamanager.ExecuteQuery(query, "Comments", null);
            var commentses = (from DataRow row in table.Rows
                              select new Comments(row)).OrderBy(c => c.DateCreated).ToList();
            return commentses;
        }
        public void ModerateComment(bool value, string id)
        {
            var query = "UPDATE [Comments] SET [cmIsApproved]=@1 WHERE [cmId]=@2";
            var parameter = new SqlParameter[2];
            parameter[0] = new SqlParameter("@1", SqlDbType.TinyInt) { Value = value };
            parameter[1] = new SqlParameter("@2", SqlDbType.NVarChar) { Value = id };
            _datamanager.ExecuteNonQuery(query, parameter);
        }
    }
}
